﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Quest.Core.DataAccess
{
    /// <summary>
    /// MSSQL数据库访问
    /// </summary>
    [AccessProvider(ProviderName = "MSSQL")]
    internal class MSSqlServer : IServer
    {
        public MSSqlServer() { }
        public DbConnection Connection { get; set; }
        public DbCommand Command { get; set; }
        public DbTransaction Transaction { get; set; }
        public IList<DbParameter> Parameters { get; set; }
        public string ConnectionString { get; set; }

        /// <summary>
        /// 初始化类
        /// </summary>
        /// <param name="connectionString"></param>
        public void Init(string connectionString)
        {
            ConnectionString = connectionString;
            Connection = new SqlConnection(ConnectionString);
            Parameters = new List<DbParameter>();
        }

        #region 增加参数  

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="name">参数名（自动添加参数特殊字符@）</param>
        /// <param name="dbType">数据类型</param>
        /// <param name="direction">参数类型</param>
        /// <returns></returns>
        private DbParameter CreateParameter(string name, DbType dbType, ParameterDirection direction)
        {
            DbParameter dbParameter = new SqlParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = "@" + name;
            dbParameter.Direction = direction;
            return dbParameter;
        }

        /// <summary>
        /// 增加输入参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        public void AddParameter(string name, DbType dbType, object value)
        {
            DbParameter dbParameter = CreateParameter(name, dbType, ParameterDirection.Input);
            dbParameter.Value = value;
            Parameters.Add(dbParameter);
        }

        /// <summary>
        /// 增加输出参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        public void AddOutParameter(string name, DbType dbType, int size)
        {
            DbParameter dbParameter = CreateParameter(name, dbType, ParameterDirection.Output);
            dbParameter.Size = size;
            Parameters.Add(dbParameter);
        }

        /// <summary>
        /// 增加返回参数
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        public void AddReturnParameter(string name, DbType dbType)
        {
            DbParameter dbParameter = CreateParameter(name, dbType, ParameterDirection.ReturnValue);
            Parameters.Add(dbParameter);
        }

        #endregion

        #region 数据库连接

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(ConnectionString);
            }
            if (Command == null)
            {
                Command = Connection.CreateCommand();
            }
            Command.Parameters.Clear();
            foreach (SqlParameter item in Parameters)
            {
                Command.Parameters.Add(item);
            }
            if (Connection.State != ConnectionState.Open)
            {
                Connection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (Transaction == null)
            {
                if (Connection != null)
                {
                    Connection.Close();
                    Connection.Dispose();
                    Connection = null;
                    Command.Dispose();
                    Command = null;
                }
            }
            Parameters = new List<DbParameter>();
        }

        #endregion

        #region 数据库操作

        public DataSet ExecuteDataSet(string text, CommandType type = CommandType.Text)
        {
            try
            {
                Open();
                Command.CommandType = type;
                Command.CommandText = text;
                SqlDataAdapter adp = new SqlDataAdapter(Command as SqlCommand);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                Close();
                return ds;
            }
            catch (Exception e)
            {
                Close();
                throw e;
            }
        }

        public DataTable ExecuteDataTable(string text, CommandType type = CommandType.Text)
        {
            try
            {
                Open();
                Command.CommandType = type;
                Command.CommandText = text;
                SqlDataAdapter adp = new SqlDataAdapter(Command as SqlCommand);
                DataTable dt = new DataTable();
                adp.Fill(dt);
                Close();
                return dt;
            }
            catch (Exception e)
            {
                Close();
                throw e;
            }
        }

        public DbDataReader ExecuteReader(string text, CommandType type = CommandType.Text)
        {
            try
            {
                Open();
                Command.CommandType = type;
                Command.CommandText = text;
                var result = Command.ExecuteReader();
                Close();
                return result;
            }
            catch (Exception e)
            {
                Close();
                throw e;
            }
        }

        public int ExecuteNonQuery(string text, CommandType type = CommandType.Text)
        {
            try
            {
                Open();
                Command.CommandType = type;
                Command.CommandText = text;
                var result = Command.ExecuteNonQuery();
                Close();
                return result;
            }
            catch (Exception e)
            {
                Close();
                throw e;
            }
        }

        public object ExecuteScalar(string text, CommandType type = CommandType.Text)
        {
            try
            {
                Open();
                Command.CommandType = type;
                Command.CommandText = text;
                var result = Command.ExecuteScalar();
                Close();
                return result;
            }
            catch (Exception e)
            {
                Close();
                throw e;
            }
        }
        #endregion
    }
}
